﻿using System;
using System.Collections.Generic;
using System.Linq;

namespace eStore.Business
{
    public class eStoreDB
    {
        readonly eStoreDC db = new eStoreDC();

        public object GeteStoreCategoriesByModuleID(int moduleID, out int totalRecords)
        {
            var result = from p in db.ud_eStoreCategories
                         where p.ModuleID.Equals(moduleID)
                         select new
                         {
                             p.CategoryID,
                             p.ModuleID,
                             p.Name,
                             p.ParentID,
                             Sum = 1,
                             ParentName = ((from q in db.ud_eStoreCategories
                                            where q.CategoryID.Equals(p.ParentID)
                                            select q).FirstOrDefault().Name)

                         };
            totalRecords = result.Count();
            return result.ToList();
        }

        public List<ud_eStoreCategory> GeteStoreRootCategoriesByModuleID(int moduleID)
        {
            var result = from p in db.ud_eStoreCategories
                         where p.ParentID.Equals(0) && p.ModuleID.Equals(moduleID)
                         select p;
            return result.ToList();
        }

        public List<ud_eStoreCategory> GeteStoreChildCategoryByCatID(int categoryID)
        {
            var result = from p in db.ud_eStoreCategories
                         where p.ParentID.Equals(categoryID)
                         select p;
            return result.ToList();
        }

        public ud_eStoreCategory GeteStoreCategoryByCatID(int categoryID)
        {
            var result = (from p in db.ud_eStoreCategories
                          where p.CategoryID.Equals(categoryID)
                          select p).FirstOrDefault();
            return result;
        }

        public List<ud_eStoreCategory> GeteStoreParentCategoryByCatID(int categoryID)
        {
            var result = from p in db.ud_eStoreCategories
                         where p.ParentID.Equals(categoryID)
                         select p;
            return result.ToList();
        }

        public void InserteStoreCategory(int moduleID, string name, int parentID)
        {
            ud_eStoreCategory category = new ud_eStoreCategory
            {
                ModuleID = moduleID,
                Name = name,
                ParentID = parentID
            };
            db.ud_eStoreCategories.InsertOnSubmit(category);
            db.SubmitChanges();
        }

        public void UpdateeStoreCategory(int categoryID, int moduleID, string name, int parentID)
        {
            ud_eStoreCategory category = GeteStoreCategoryByCatID(categoryID);
            if (category != null)
            {
                category.ModuleID = moduleID;
                category.Name = name;
                category.ParentID = parentID;
                db.SubmitChanges();
            }
        }

        public void DeleteeStoreCategory(int categoryID)
        {
            ud_eStoreCategory category = GeteStoreCategoryByCatID(categoryID);
            if (category != null)
            {
                List<ud_eStoreCategory> children = GeteStoreParentCategoryByCatID(categoryID);
                List<ud_eStoreProduct> products = GeteStoreProductByCatID(categoryID);
                db.ud_eStoreProducts.DeleteAllOnSubmit(products);
                db.ud_eStoreCategories.DeleteAllOnSubmit(children);
                db.ud_eStoreCategories.DeleteOnSubmit(category);
                db.SubmitChanges();
            }
        }

        public object GeteStoreProductByModuleID(Guid storeGuid, out int totalRecords)
        {
            var result = from p in db.ud_eStoreProducts
                         where p.StoreGuid.Equals(storeGuid)
                         orderby p.LastModified descending
                         select new
                         {
                             p.Guid,
                             p.Name,
                             p.IsVisible,
                             p.IsSpecial,
                             p.Url,
                             CategoryName = ((from q in db.ud_eStoreCategories
                                              where q.CategoryID.Equals(p.CategoryID)
                                              select new { q.Name }).FirstOrDefault().Name
                             )
                         };
            totalRecords = result.Count();
            return result.ToList();
        }

        public object GeteStoreOtherProductByStoreGuid(Guid storeGuid, Guid productGuid, out int totalRecords)
        {
            var result = from p in db.ud_eStoreProducts
                         where p.StoreGuid.Equals(storeGuid) && !p.Guid.Equals(productGuid)
                         orderby p.LastModified descending
                         select new
                         {
                             p.Guid,
                             p.Name,
                             p.IsDeleted,
                             p.IsSpecial,
                             p.Url,
                             CategoryName = ((from q in db.ud_eStoreCategories
                                              where q.CategoryID.Equals(p.CategoryID)
                                              select new { q.Name }).FirstOrDefault().Name)
                         };
            totalRecords = result.Count();
            return result.ToList();
        }

        public List<ud_eStoreProduct> GeteStoreOtherSimilarProduct(Guid storeGuid, Guid productGuid, int categoryID)
        {
            var result = db.ud_eStoreProducts.Where(
                p => p.StoreGuid.Equals(storeGuid) && !p.Guid.Equals(productGuid) && p.CategoryID == categoryID)
                .OrderBy(p => p.SortRank1).ThenBy(p => p.SortRank2).ThenByDescending(p => p.LastModified);
            return result.ToList();
        }

        public List<ud_eStoreProduct> GeteStoreOtherProduct(Guid storeGuid, Guid productGuid)
        {
            var result = db.ud_eStoreProducts.Where(
                p => p.StoreGuid.Equals(storeGuid) && !p.Guid.Equals(productGuid))
                .OrderBy(p => p.SortRank1).ThenBy(p => p.SortRank2).ThenByDescending(p => p.LastModified);
            return result.ToList();
        }

        public List<ud_eStoreProduct> GeteStoreProductByCatID(int categoryID)
        {
            var result = from p in db.ud_eStoreProducts
                         where p.CategoryID.Equals(categoryID)
                         select p;
            return result.ToList();
        }

        public ud_eStoreProduct GeteStoreProductByProductGuid(Guid productGuid)
        {
            var result = from p in db.ud_eStoreProducts
                         where p.Guid.Equals(productGuid)
                         select p;
            return result.FirstOrDefault();
        }

        public bool DeleteeStoreProductByProductGuid(Guid productGuid)
        {
            bool flag = false;
            try
            {
                ud_eStoreProduct result = GeteStoreProductByProductGuid(productGuid);
                db.ud_eStoreProducts.DeleteOnSubmit(result);
                db.SubmitChanges();
                flag = true;
            }
            catch { }
            return flag;
        }

        public List<ud_eStoreProduct> GeteStoreProductByCatIDPaging(Guid storeGuid, int categoryID, int pageNumber, int pageSize, out int totalRecords)
        {
            var result = from p in db.ud_eStoreProducts
                         where p.CategoryID.Equals(categoryID) && p.StoreGuid.Equals(storeGuid) && !p.IsDeleted
                         select p;
            totalRecords = result.Count();
            result = result.Skip((pageNumber - 1) * pageSize).Take(pageSize);
            return result.ToList();
        }

        public List<ud_eStoreProduct> GeteStoreProductPaging(Guid storeGuid, int pageNumber, int pageSize, out int totalRecords)
        {
            var result = db.ud_eStoreProducts.Where(p => p.StoreGuid.Equals(storeGuid) && !p.IsDeleted)
                .OrderBy(p => p.SortRank1).ThenBy(p => p.SortRank2).ThenByDescending(p => p.LastModified).ToList();
            totalRecords = result.Count();
            return result.Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList();
        }
        public List<ud_eStoreProduct> GeteStoreSpecialProductByCatIDPaging(Guid storeGuid, int categoryID, int pageNumber, int pageSize, out int totalRecords)
        {
            var result = db.ud_eStoreProducts.Where(p => p.CategoryID.Equals(categoryID) && p.StoreGuid.Equals(storeGuid) && !p.IsDeleted && p.IsSpecial)
                .OrderBy(p => p.SortRank1).ThenBy(p => p.SortRank2).ThenByDescending(p => p.LastModified).ToList();
            totalRecords = result.Count();
            return result.Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList();
        }

        public List<ud_eStoreProduct> GeteStoreSpecialProductPaging(Guid storeGuid, int pageNumber, int pageSize, out int totalRecords)
        {
            var result = db.ud_eStoreProducts.Where(p => p.StoreGuid.Equals(storeGuid) && !p.IsDeleted && p.IsSpecial)
                .OrderBy(p => p.SortRank1).ThenBy(p => p.SortRank2).ThenByDescending(p => p.LastModified).ToList();
            totalRecords = result.Count();
            return result.Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList();
        }

        public List<ud_eStoreProduct> GeteStoreSpecialProductPaging(Guid storeGuid, int pageNumber, int pageSize, out int totalRecords, bool showLastestFirst)
        {
            var result = db.ud_eStoreProducts.Where(p => p.StoreGuid.Equals(storeGuid) && !p.IsDeleted && p.IsSpecial)
                .OrderBy(p => p.SortRank1).ThenBy(p => p.SortRank2).ThenByDescending(p => p.LastModified).ToList();
            totalRecords = result.Count();
            return result.Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList();
        }
    }
}
